-- Script de création de la base de données
-- By: Bernard Pratz

SET NOCOUNT ON

-- Drop Foreign Keys
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Film_Award') and o.name = 'FK_FILM_AWARD_FILM')
alter table Film_Award
    DROP CONSTRAINT FK_FILM_AWARD_FILM
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Film_Award') and o.name = 'FK_FILM_AWARD_AWARD')
alter table Film_Award
    DROP CONSTRAINT FK_FILM_AWARD_AWARD
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Film_Genre') and o.name = 'FK_FILM_GENRE_FILM')
alter table Film_Genre
    DROP CONSTRAINT FK_FILM_GENRE_FILM
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Film_genre') and o.name = 'FK_FILM_GENRE_GENRE')
alter table Film_Genre 
    DROP CONSTRAINT FK_FILM_GENRE_GENRE
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Played_In') and o.name = 'FK_PLAYED_IN_FILM')
alter table Played_In 
    DROP CONSTRAINT FK_PLAYED_IN_FILM
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Played_In') and o.name = 'FK_PLAYED_IN_PERSON')
alter table Played_In 
    DROP CONSTRAINT FK_PLAYED_IN_PERSON
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Subscription') and o.name = 'FK_SUBSCRIPTION_SUBSCRIBER')
alter table Subscription
    DROP CONSTRAINT FK_SUBSCRIPTION_SUBSCRIBER
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Edition') and o.name = 'FK_EDITION_FILM')
alter table Edition
    DROP CONSTRAINT FK_EDITION_FILM
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Translated_In') and o.name = 'FK_TRANS_IN_EDITION')
alter table Translated_In
    DROP CONSTRAINT FK_TRANS_IN_EDITION
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Translated_In') and o.name = 'FK_TRANS_IN_LANG')
alter table Translated_In
    DROP CONSTRAINT FK_TRANS_IN_LANG
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Booking') and o.name = 'FK_BOOKING_EDITION')
alter table Booking
    DROP CONSTRAINT FK_BOOKING_EDITION
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Booking') and o.name = 'FK_BOOKING_SUBSCRIPTION')
alter table Booking
    DROP CONSTRAINT FK_BOOKING_SUBSCRIPTION
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Copy') and o.name = 'FK_COPY_EDITION')
alter table Copy 
    DROP CONSTRAINT FK_COPY_EDITION
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Rent') and o.name = 'FK_RENT_SUBSCRIPTION')
alter table Rent
    DROP CONSTRAINT FK_RENT_SUBSCRIPTION
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Rent') and o.name = 'FK_RENT_COPY')
alter table Rent 
    DROP CONSTRAINT FK_RENT_COPY
go
if exists (select 1
   from dbo.sysreferences r join dbo.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('Recovery') and o.name = 'FK_RECOVERY_RENT')
alter table Recovery 
    DROP CONSTRAINT FK_RECOVERY_RENT
go
-- Drop indexes
-- Drop tables
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Award') and type = 'U')             DROP TABLE Award;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Genre') and type = 'U')             DROP TABLE Genre;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Person') and type = 'U')            DROP TABLE Person;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Film') and type = 'U')              DROP TABLE Film;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Film_Award') and type = 'U')        DROP TABLE Film_Award;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Film_Genre') and type = 'U')        DROP TABLE Film_Genre;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Played_in') and type = 'U')         DROP TABLE Played_in;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Subscriber') and type = 'U')        DROP TABLE Subscriber;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Subscription') and type = 'U')      DROP TABLE Subscription;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Edition') and type = 'U')           DROP TABLE Edition;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Language') and type = 'U')          DROP TABLE Language;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Subscription_Type') and type = 'U') DROP TABLE Subscription_Type;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Translated_In') and type = 'U')     DROP TABLE Translated_In;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Booking') and type = 'U')           DROP TABLE Booking;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Copy') and type = 'U')              DROP TABLE Copy;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Rent') and type = 'U')              DROP TABLE Rent;
go
if EXISTS (SELECT 1 FROM SysObjects WHERE  id = object_id('Recovery') and type = 'U')          DROP TABLE Recovery;
go


--

CREATE TABLE Award             (name                NVARCHAR(256) NOT NULL, 
                                year                INTEGER NOT NULL,
                                                    CONSTRAINT PK_AWARD PRIMARY KEY (name, year) );
go
CREATE TABLE Genre             (name                NVARCHAR(64) PRIMARY KEY NOT NULL);
go
CREATE TABLE Person            (firstname           NVARCHAR(64) NOT NULL,
                                lastname            NVARCHAR(64) NOT NULL,
                                birthdate           SMALLDATETIME,
                                country             NVARCHAR(64),
                                                    CONSTRAINT PK_PERSON PRIMARY KEY (firstname, lastname));

go
CREATE TABLE Film              (isan                BIGINT PRIMARY KEY NOT NULL,
                                title               NVARCHAR(64) NOT NULL,
                                complement          NVARCHAR(64),
                                release_date        NVARCHAR(64),
                                has_no_copy         INTEGER); -- TODO DEFAULT "1"

go
CREATE TABLE Film_Award        (id_film             BIGINT NOT NULL,
                                award_name          NVARCHAR(256) NOT NULL,
                                award_year          INTEGER NOT NULL,
                                                    CONSTRAINT PK_FILM_AWARD PRIMARY KEY (id_film, award_name),
                                                    CONSTRAINT FK_FILM_AWARD_FILM FOREIGN KEY (id_film) REFERENCES Film (isan) ON DELETE CASCADE,
                                                    CONSTRAINT FK_FILM_AWARD_AWARD FOREIGN KEY (award_name, award_year) REFERENCES Award (name, year) ON DELETE CASCADE);
go
CREATE TABLE Film_Genre        (id_film             BIGINT NOT NULL,
                                genre_name          NVARCHAR(64) NOT NULL,
                                                    CONSTRAINT PK_FILM_GENRE PRIMARY KEY (id_film, genre_name),
                                                    CONSTRAINT FK_FILM_GENRE_FILM FOREIGN KEY (id_film) REFERENCES Film (isan) ON DELETE CASCADE,
                                                    CONSTRAINT FK_FILM_GENRE_GENRE FOREIGN KEY (genre_name) REFERENCES Genre (name) ON DELETE CASCADE);
go
CREATE TABLE Played_in         (id_film             BIGINT NOT NULL,
                                firstname           NVARCHAR(64) NOT NULL,
                                lastname            NVARCHAR(64) NOT NULL,
                                role                NVARCHAR(64) NOT NULL,
--                                                    CONSTRAINT PK_PLAYED_IN PRIMARY KEY (id_film, firstname, lastname),
                                                    CONSTRAINT FK_PLAYED_IN_FILM FOREIGN KEY (id_film) REFERENCES Film (isan) ON DELETE CASCADE,
                                                    CONSTRAINT FK_PLAYED_IN_PERSON FOREIGN KEY (firstname, lastname) REFERENCES Person (firstname, lastname) ON DELETE CASCADE);
go
--
CREATE TABLE Subscriber        (firstname           NVARCHAR(64) NOT NULL,
                                lastname            NVARCHAR(64) NOT NULL, 
                                birthdate           SMALLDATETIME    NOT NULL,
                                phone               NVARCHAR(16),
                                e_mail              NVARCHAR(64),
                                street_number       NVARCHAR(8), 
                                street_extra        NVARCHAR(16),
                                street_kind         NVARCHAR(16),
                                street_name         NVARCHAR(128),
                                postcode            NVARCHAR(5),
                                city                NVARCHAR(128),
                                country             NVARCHAR(64),
                                blacklisted         INTEGER NOT NULL,
                                                    CONSTRAINT PK_SUBSCRIBER PRIMARY KEY (firstname, lastname, birthdate));
go
                            
CREATE TABLE Subscription_Type (name                NVARCHAR(64) PRIMARY KEY NOT NULL);
go
CREATE TABLE Subscription      (id                  INTEGER PRIMARY KEY IDENTITY NOT NULL,
                                balance             INTEGER NOT NULL,
                                subscription_type   NVARCHAR(64) NOT NULL,
                                firstname           NVARCHAR(64) NOT NULL,
                                lastname            NVARCHAR(64) NOT NULL,
                                birthdate           SMALLDATETIME NOT NULL,
                                begin_time          SMALLDATETIME NOT NULL,
                                end_time            SMALLDATETIME NOT NULL,
                                                    CONSTRAINT FK_SUBSCRIPTION_SUBSCRIBTY FOREIGN KEY (subscription_type) 
                                                                                REFERENCES Subscription_Type (name),
                                                    CONSTRAINT FK_SUBSCRIPTION_SUBSCRIBER FOREIGN KEY (firstname, lastname, birthdate) 
                                                                                REFERENCES Subscriber (firstname, lastname, birthdate) ON DELETE CASCADE);
go

CREATE TABLE Edition           (id                  INTEGER PRIMARY KEY IDENTITY NOT NULL,
                                id_film             BIGINT NOT NULL,
                                support             NVARCHAR(64) NOT NULL,
                                release_date        SMALLDATETIME,
                                extras              NVARCHAR(256)
                                                    CONSTRAINT FK_EDITION_FILM FOREIGN KEY (id_film) REFERENCES Film (isan) ON DELETE CASCADE);
go

CREATE TABLE Language          (language_name       NVARCHAR(64) PRIMARY KEY NOT NULL);
go

CREATE TABLE Translated_In     (id_edition          INTEGER NOT NULL,
                                language_name       NVARCHAR(64) NOT NULL,
                                translation_type    NVARCHAR(64) NOT NULL
--                                                    CONSTRAINT PK_TRANS_IN PRIMARY KEY (id_edition, language_name),
                                                    CONSTRAINT FK_TRANS_IN_EDITION FOREIGN KEY (id_edition) REFERENCES Edition (id) ON DELETE CASCADE,
                                                    CONSTRAINT FK_TRANS_IN_LANG FOREIGN KEY (language_name) REFERENCES Language (language_name) ON DELETE CASCADE);
go
CREATE TABLE Booking           (id                  INTEGER PRIMARY KEY IDENTITY NOT NULL,
                                id_edition          INTEGER NOT NULL,
                                id_subscription     INTEGER NOT NULL,
                                booked_time         SMALLDATETIME NOT NULL,
                                expiry_date         SMALLDATETIME NOT NULL
                                                    CONSTRAINT FK_BOOKING_EDITION FOREIGN KEY (id_edition) REFERENCES Edition (id) ON DELETE CASCADE,
                                                    CONSTRAINT FK_BOOKING_SUBSCRIPTION FOREIGN KEY (id_subscription) 
                                                                                                           REFERENCES Subscription (id) ON DELETE CASCADE);
go
--
CREATE TABLE Copy              (id                  INTEGER PRIMARY KEY IDENTITY NOT NULL,
                                id_edition          INTEGER NOT NULL,
                                to_be_deleted       INTEGER DEFAULT 0,
                                rented              INTEGER DEFAULT 0,
                                booked              INTEGER DEFAULT 0
                                                    CONSTRAINT FK_COPY_EDITION FOREIGN KEY (id_edition) REFERENCES Edition (id) ON DELETE CASCADE);
go

CREATE TABLE Rent              (id                  INTEGER PRIMARY KEY IDENTITY NOT NULL,
                                id_subscription     INTEGER NOT NULL,
                                id_copy             INTEGER NOT NULL,
                                rent_time           SMALLDATETIME NOT NULL,
                                planned_delivery    SMALLDATETIME NOT NULL,
                                actual_delivery     SMALLDATETIME
                                                    CONSTRAINT FK_RENT_SUBSCRIPTION FOREIGN KEY (id_subscription) REFERENCES Subscription (id) ON DELETE CASCADE,
                                                    CONSTRAINT FK_RENT_COPY FOREIGN KEY (id_copy) REFERENCES Copy (id) ON DELETE CASCADE);
go
CREATE TABLE Recovery          (id                  INTEGER PRIMARY KEY IDENTITY NOT NULL,
                                id_rent             INTEGER NOT NULL,
                                recovering_level    INTEGER NOT NULL,
                                time                SMALLDATETIME NOT NULL
                                                    CONSTRAINT FK_RECOVERY_RENT FOREIGN KEY (id_rent) REFERENCES Rent (id) ON DELETE CASCADE);
go
